<?php
class ModelSaleAffiliate extends Model {
	public function addAffiliate($data) {
		$data['date_added'] = $GLOBALS['V5_NOW'];
		$data['salt'] = substr(md5(uniqid(rand(), true)), 0, 9);
		$data['password'] = sha1($data['salt'] . sha1($data['salt'] . sha1($data['password'])));

		$this->db->insert('affiliate', $data);
	}

	public function editAffiliate($affiliate_id, $data) {
		if (!empty($data['password'])) {
			$data['salt'] = substr(md5(uniqid(rand(), true)), 0, 9);
			$data['password'] = sha1($data['salt'] . sha1($data['salt'] . sha1($data['password'])));
		} else unset($data['password']);
		
		$this->db->update('affiliate', $data, array('affiliate_id' => $affiliate_id));
	}

	public function deleteAffiliate($affiliate_id) {
		$this->db->query("DELETE FROM  @@affiliate WHERE affiliate_id = " . (int)$affiliate_id);
		$this->db->query("DELETE FROM  @@affiliate_transaction WHERE affiliate_id = " . (int)$affiliate_id);
	}

	public function getAffiliate($affiliate_id) {
		return $this->db->queryOne("SELECT * FROM  @@affiliate WHERE affiliate_id = " . (int)$affiliate_id);
	}

	public function getAffiliateByEmail($email) {
		return $this->db->queryOne("SELECT * FROM  @@affiliate WHERE email = '" . ES($email) . "'");
	}

	public function getAffiliates($filter = null) {
		$sql = "SELECT *, CONCAT(a.firstname, ' ', a.lastname) AS name, (SELECT SUM(at.amount) FROM  @@affiliate_transaction at WHERE at.affiliate_id = a.affiliate_id GROUP BY at.affiliate_id) AS balance FROM  @@affiliate a WHERE 1";

		if ($filter->get('filter_name')) {
			$sql .= " AND CONCAT(a.firstname, ' ', a.lastname) LIKE '" . ES($filter->get('filter_name')) . "%'";
		}

		if ($filter->get('filter_email')) {
			$sql .= " AND a.email = '" . ES($filter->get('filter_email')) . "'";
		}

		if ($filter->get('filter_code')) {
			$sql .= " AND a.code = '" . ES($filter->get('filter_code')) . "'";
		}

		if ($filter->get('filter_status') !== null) {
			$sql .= " AND a.status = " . (int)$filter->get('filter_status');
		}

		if ($filter->get('filter_approved') !== null) {
			$sql .= " AND a.approved = " . (int)$$filter->get('filter_approved');
		}

		if ($filter->get('filter_date_added')) {
			$sql .= " AND DATE(a.date_added) = '" . ES($filter->get('filter_date_added')) . "'";
		}

		$sql .= $filter->sort('name', 'ASC');
		$sql .= $filter->limit();
		
		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function approve($affiliate_id) {
		$affiliate_info = $this->getAffiliate($affiliate_id);

		if ($affiliate_info) {
			$this->db->query("UPDATE  @@affiliate SET approved = 1 WHERE affiliate_id = " . (int)$affiliate_id);
			$params = array(
				'{affiliate_firstname}' => $affiliate_info['firstname'],
				'{affiliate_lastname}' => $affiliate_info['lastname'],
				'{affiliate_email}' => $affiliate_info['email']
			);
			send_mail($affiliate_info['email'], 'affiliate_commission', $params);
		}
	}

	public function getAffiliatesByNewsletter() {
		$query = $this->db->query("SELECT * FROM  @@affiliate WHERE newsletter = '1' ORDER BY firstname, lastname, email");

		return $query->rows;
	}

	public function getTotalAffiliates($filter = null) {
      	$sql = "SELECT COUNT(*) AS total FROM  @@affiliate a WHERE 1";
		
      	if ($filter) {
	      	if ($filter->get('filter_name')) {
	      		$sql .= " AND CONCAT(a.firstname, ' ', a.lastname) LIKE '" . ES($filter->get('filter_name')) . "%'";
	      	}
	      	
	      	if ($filter->get('filter_email')) {
	      		$sql .= " AND a.email = '" . ES($filter->get('filter_email')) . "'";
	      	}
	      	
	      	if ($filter->get('filter_code')) {
	      		$sql .= " AND a.code = '" . ES($filter->get('filter_code')) . "'";
	      	}
	      	
	      	if ($filter->get('filter_status') !== null) {
	      		$sql .= " AND a.status = " . (int)$filter->get('filter_status');
	      	}
	      	
	      	if ($filter->get('filter_approved') !== null) {
	      		$sql .= " AND a.approved = " . (int)$$filter->get('filter_approved');
	      	}
	      	
	      	if ($filter->get('filter_date_added')) {
	      		$sql .= " AND DATE(a.date_added) = '" . ES($filter->get('filter_date_added')) . "'";
	      	}
      	}
		return $this->db->queryOne($sql);
	}

	public function getTotalAffiliatesAwaitingApproval() {
      	return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@affiliate WHERE status = 0 OR approved = 0");
	}

	public function getTotalAffiliatesByCountryId($country_id) {
		return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@affiliate WHERE country_id = " . (int)$country_id);
	}

	public function getTotalAffiliatesByZoneId($zone_id) {
		return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@affiliate WHERE zone_id = " . (int)$zone_id);
	}

	public function addTransaction($affiliate_id, $description = '', $amount = '', $order_id = 0) {
		$affiliate_info = $this->getAffiliate($affiliate_id);

		if ($affiliate_info) {
			$this->db->query("INSERT INTO  @@affiliate_transaction SET affiliate_id = '" . (int)$affiliate_id . "', order_id = '" . (float)$order_id . "', description = '" . ES($description) . "', amount = '" . (float)$amount . "', date_added = '{$GLOBALS['V5_NOW']}'");

			$params = array(
				'{affiliate_firstname}' => $affiliate_info['firstname'],
				'{affiliate_lastname}' => $affiliate_info['lastname'],
				'{affiliate_email}' => $affiliate_info['email'],
				'{affiliate_amount_received}' => $this->currency->format($amount, C('config_currency')),
				'{affiliate_total_amount}' => $this->currency->format($this->getTransactionTotal($affiliate_id), C('config_currency'))
			);
			
			send_mail($affiliate_info['email'], 'affiliate_commission', $params);
		}
	}

	public function deleteTransaction($order_id) {
		$this->db->query("DELETE FROM  @@affiliate_transaction WHERE order_id = " . (int)$order_id);
	}

	public function getTransactions($affiliate_id, $start = 0, $limit = 10) {
		if ($start < 0) $start = 0;
		if ($limit < 1) $limit = 10;

		$query = $this->db->query("SELECT * FROM  @@affiliate_transaction WHERE affiliate_id = " . (int)$affiliate_id . " ORDER BY date_added DESC LIMIT " . (int)$start . "," . (int)$limit);

		return $query->rows;
	}

	public function getTotalTransactions($affiliate_id) {
		return $this->db->queryOne("SELECT COUNT(*) AS total  FROM  @@affiliate_transaction WHERE affiliate_id = " . (int)$affiliate_id);
	}

	public function getTransactionTotal($affiliate_id) {
		return $this->db->queryOne("SELECT SUM(amount) AS total FROM  @@affiliate_transaction WHERE affiliate_id = " . (int)$affiliate_id);
	}

	public function getTotalTransactionsByOrderId($order_id) {
		return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@affiliate_transaction WHERE order_id = " . (int)$order_id);
	}
}
?>